package com.yunji.aggregate.mapper;

import cn.hutool.core.lang.Pair;
import com.yunji.aggregate.model.dto.analysis.*;
import com.yunji.aggregate.model.dto.base.AllianceCountParam;
import com.yunji.aggregate.model.dto.base.AuditCountParam;
import com.yunji.aggregate.model.dto.base.ProductAgentCountParam;
import com.yunji.aggregate.model.dto.container.CabinetCountDTO;
import com.yunji.aggregate.model.dto.container.FillCargoCountParam;
import com.yunji.aggregate.model.vo.analysis.*;
import com.yunji.common.annotation.DataScope;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;

/**
 * @author : peter-zhu
 * @date : 2024/12/20 10:38
 * @description : 所有统计简单的查询  没有xml
 **/
public interface IAnalysisSimpleMapper {

    @DataScope
    @Select({"<script>SELECT DATE_FORMAT(ord.pay_time,'%m-%d') AS date, IFNULL(SUM(ord.actual_amount),0) AS orderAmount,",
            " IFNULL(count(*),0) AS orderNums  FROM t_order ord WHERE  ord.order_status = 2 ",
            "AND ord.pay_time &gt;= #{beginDay} AND ord.pay_time &lt;= #{closeDay} GROUP BY date</script>"})
    List<SaleTrendVO> countSaleTrendOfDay(@Param("param") HomeDataParam param, @Param("beginDay") LocalDateTime beginDay, @Param("closeDay") LocalDateTime closeDay);

    @DataScope
    @Select({"<script>SELECT DATE_FORMAT(ord.pay_time,'%Y-%m') AS date, IFNULL(SUM(ord.actual_amount),0) AS orderAmount, ",
            " IFNULL(count(*),0) AS orderNums  FROM t_order ord  WHERE ord.order_status = 2 ",
            "AND ord.pay_time &gt;= #{beginDay} AND ord.pay_time &lt;= #{closeDay}  GROUP BY date</script>"})
    List<SaleTrendVO> countSaleTrendOfMonth(@Param("param") HomeDataParam param, @Param("beginDay") LocalDateTime beginDay, @Param("closeDay") LocalDateTime closeDay);

    @Select({"<script>SELECT DATE_FORMAT(ord.pay_time,'%m-%d') AS date, IFNULL(SUM(ord.actual_amount),0) AS orderAmount,",
            " IFNULL(SUM(ord.cost_price),0) AS costPrice FROM t_order ord  WHERE ord.order_status = 2 ",
            "AND ord.pay_time &gt;= #{beginDay}  AND ord.pay_time &lt;= #{closeDay} ",
            "GROUP BY date</script>"})
    List<SaleTrendVO> countAdminTrendOfDay(@Param("beginDay") LocalDateTime beginDay, @Param("closeDay") LocalDateTime closeDay);

    @Select({"<script> SELECT DATE_FORMAT(ord.pay_time,'%Y-%m') AS date,  IFNULL(SUM(ord.actual_amount),0) AS orderAmount, ",
            "IFNULL(SUM(ord.cost_price),0) AS costPrice  FROM  t_order ord ",
            "WHERE  ord.order_status = 2 ",
            "AND ord.pay_time &gt;= #{beginDay} AND ord.pay_time &lt;= #{closeDay} GROUP BY date</script>"})
    List<SaleTrendVO> countAdminTrendOfMonth(@Param("beginDay") LocalDateTime beginDay, @Param("closeDay") LocalDateTime closeDay);

    @DataScope
    @Select({"<script>select od.product_name productName, ifnull(sum(od.payable_amount),0) productAmount," +
            " ifnull(count(*),0) productCount  from t_order o,t_order_detail od  where o.order_id=od.order_id " ,
            " and o.order_status = 2 and o.pay_time &gt;= #{beginDay} and o.pay_time &lt;= #{closeDay} ",
            " group by od.product_name order by productAmount desc</script>"})
    List<ProductRatioVO> salesOfProducts(@Param("param") HomeDataParam param, @Param("beginDay") LocalDateTime beginDay, @Param("closeDay") LocalDateTime closeDay);

    @DataScope
    @Select("SELECT IFNULL(SUM(actual_amount),0) FROM t_order t WHERE t.order_status=2 AND DATE(t.create_time)=CURDATE()")
    BigDecimal countOrderAmount(HomeDataParam param);

    @DataScope
    @Select("SELECT IFNULL(COUNT(*),0) FROM t_order t  WHERE t.order_status = 2 AND DATE(t.create_time)=CURDATE()")
    Integer countOrderNums(HomeDataParam param);

    @Select("select IFNULL(SUM(trade_amount),0) from t_trade where belong_id=#{belongId} AND DATE(create_time)=CURDATE()")
    BigDecimal countOrderEarns(HomeDataParam param);

    /*-------------------------------------------库存统计------------------------------------------*/
    @Select({"<script>SELECT IFNULL(SUM(usable_stock),0) FROM v3_stock_table WHERE del_flag = 0 AND belong_id = #{belongId}</script>"})
    BigDecimal allUsableStock(Long belongId);

    @Select({"<script>SELECT COUNT(*) FROM v3_stock_table WHERE del_flag = 0 AND usable_stock &lt; warm_value AND belong_id = #{belongId}</script>"})
    Integer warnStock(Long belongId);

    @Select({"<script>SELECT IFNULL(COUNT(*),0) FROM v3_discard_ticket WHERE del_flag = 0",
            "<if test='auditStatus != null'>AND audit_status = #{auditStatus}</if>",
            "<if test='belongId != null'>AND (belong_id = #{belongId} OR out_belong_id = #{belongId})</if></script>"})
    Integer countDiscard(AuditCountParam param);

    @Select({"<script>SELECT  IFNULL(COUNT(*),0) FROM s_alliance  WHERE del_flag = 0 ",
            "<if test='memberId != null'> AND  member_id = #{memberId}</if> ",
            "<if test='allianceType != null'> AND  alliance_type = #{allianceType}</if> ",
            "<if test='allianceStatus != null'> AND  alliance_status = #{allianceStatus} </if></script>"})
    Integer countAlliance(AllianceCountParam param);

    @Select({"<script>SELECT IFNULL(COUNT(*),0) FROM  v3_repair_ticket  WHERE del_flag = 0 ",
            "<if test='auditStatus != null'> AND  audit_status = #{auditStatus} </if> ",
            "<if test='belongId != null'>  AND  (belong_id = #{belongId} OR into_belong_id = #{belongId} OR out_belong_id = #{belongId})</if></script>"})
    Integer countRepair(AuditCountParam param);

    @Select({"<script>SELECT IFNULL(COUNT(*),0) FROM v3_stocktake_table WHERE del_flag = 0 ",
            "<if test='auditStatus != null'>AND audit_status = #{auditStatus}</if>",
            "<if test='belongId != null'>AND belong_id = #{belongId}</if></script>"})
    Integer countStockTake(AuditCountParam bo);

    @Select({"<script> SELECT IFNULL(COUNT(*),0) FROM v3_storage_ticket WHERE del_flag = 0",
            " <if test='storageType != null and storageType == 2'>AND business_type NOT IN(3,4,5,8,9,11)</if>",
            " <if test='businessType != null'>AND business_type = #{businessType}</if>",
            " <if test='storageType != null'>AND storage_type = #{storageType}</if>",
            " <if test='storageStatus != null'>AND storage_status = #{storageStatus}</if>",
            " <if test='operateStatus != null'>AND operate_status = #{operateStatus}</if>",
            " <if test='belongId != null'>AND belong_id = #{belongId}</if></script>"})
    Integer countStorageTicket(StorageTicketCountParam param);

    @Select({"<script>SELECT COUNT(1) FROM product_agent pa LEFT JOIN product p ON pa.product_id = p.id ",
            "WHERE pa.del_flag = 0 AND p.del_flag = 0 ",
            "<if test='belongId != null'>AND  belong_id=#{belongId}</if> ",
            "<if test='status != null'> AND  status= #{status}</if></script>"})
    Integer countProduct(ProductAgentCountParam param);

    @Select({"<script>SELECT IFNULL( COUNT( 1 ), 0 ) nums,DATE_FORMAT( start_time, #{sqlDateFormat} ) AS date FROM tl_maintain where del_flag = 0" +
            " <if test='staffId != null'> and staff_id = #{staffId}  </if> " +
            " <if test='startTime != null'> and DATE_FORMAT(start_time,#{sqlDateFormat}) &gt;= DATE_FORMAT(#{startTime}, #{sqlDateFormat}) </if> " +
            " <if test='endTime != null'> and DATE_FORMAT(start_time,#{sqlDateFormat}) &lt;= DATE_FORMAT(#{endTime}, #{sqlDateFormat}) </if> " +
            "GROUP BY DATE_FORMAT( start_time, #{sqlDateFormat} ) </script>"})
    List<FillCargoTimeDataVO> fillCargoNums(FillCargoCountParam param);


    @Select({"<script>SELECT IFNULL( COUNT( 1 ), 0 ) cargoNums,IFNULL( SUM(cargo_nums),0) cargoProductNums FROM tl_maintain WHERE del_flag = 0 " +
            " <if test='staffId != null'> AND staff_id = #{staffId} </if> " +
            " <if test='startTime != null'> AND  DATE_FORMAT(start_time,'%Y-%m')  &gt;= DATE_FORMAT(#{startTime} ,'%Y-%m') </if>" +
            " <if test='endTime != null'> AND  DATE_FORMAT(start_time,'%Y-%m')  &lt;=  DATE_FORMAT(#{endTime} ,'%Y-%m') </if></script>"})
    FillCargoMonthVO fillCargoDataByMonth(FillCargoCountParam param);

    @Select({"<script>select id,agent_name as name from s_agent_user where del_flag=0",
            "<if test='cooperateWay!=null'>and cooperate_way=#{cooperateWay}</if></script>"})
    List<LinkageQueryVO> agentLinkageQuery(LinkageQueryParam param);

    @Select({"<script>SELECT sit.id,sit.site_name AS name FROM t_site sit LEFT JOIN v3_belong b ON b.belong_id = sit.belong_id ",
            "LEFT JOIN v3_belong p ON p.belong_id = b.parent_id WHERE sit.del_flag = 0",
            "<if test='siteTypeIds != null and siteTypeIds.length > 0'>",
            "<foreach collection='siteTypeIds' item='siteType' open='AND  sit.site_type IN(' separator=',' close=')'>",
            "#{siteType}</foreach></if>",
            "<if test='agentIds != null and agentIds.length > 0'>",
            "<foreach collection='agentIds' item='agentId' open='AND p.agent_id  IN(' separator=',' close=')'>",
            "#{agentId}</foreach></if>",
            "<if test='scaleType != null'> AND  b.scale_type=#{scaleType}</if>",
            "<if test='parentId != null'>  AND  p.belong_id =#{parentId} </if></script>"})
    List<LinkageQueryVO> siteLinkageQuery(LinkageQueryParam param);

    @Select({"<script>SELECT  IFNULl( count( DISTINCT c.container_id ), 0 ) FROM ",
            "t_container c LEFT JOIN t_container_cell cc ON c.container_id = cc.container_id ",
            "WHERE  c.del_flag = 0 AND cc.`status` = 0 AND cc.del_flag = 0 AND c.belong_id = #{belongId} ",
            "<if test='day != null'> AND DATEDIFF(NOW(),cc.status_update_time) &gt;= 3 </if></script>"})
    Integer countFillCargoContainerNums(@Param("belongId") Long managerId, @Param("day") Integer day);

    @Select({"<script> SELECT IFNULL( count( p.id ), 0 ) FROM t_container c ",
            "LEFT JOIN t_container_cell cc ON c.container_id = cc.container_id ",
            "LEFT JOIN product p ON p.id = cc.product_id ",
            "WHERE c.del_flag = 0 AND cc.`status` = 0 AND  cc.del_flag = 0 AND c.belong_id = #{belongId} ",
            "<if test='day != null'> AND DATEDIFF(NOW(),cc.status_update_time) &gt;= 3 </if></script>"})
    Integer countFillCargoProductNums(@Param("belongId") Long belongId, @Param("day") Integer day);

    @Select({"<script> select count(*) from t_container where del_flag = 0 and staff_id = #{id} </script>"})
    List<Long> getContainerByStaffId(Long id);

    @Select({"<script> SELECT  online_status , use_status,battle_status FROM t_container",
            "  WHERE del_flag = 0  AND site_id = #{siteId} </script>"})
    List<CabinetCountDTO> getCabinetBySiteId(Long siteId);

    /*-------------------------------------------退款统计------------------------------------------*/
    @DataScope
    @Select("SELECT IFNULL(COUNT(*),0) FROM t_refund rf WHERE rf.apply_status=2 AND DATE(rf.create_time)=CURDATE()")
    Integer countOrderRefund(HomeDataParam param);

    @Select({"<script>SELECT IFNULL(COUNT(*),0) FROM t_refund t ",
            "LEFT JOIN t_order o ON t.order_id = o.order_id ",
            "WHERE 1=1 ",
            "<if test='userId != null'>AND t.user_id= #{userId}</if> ",
            "<if test='applyStatus != null'>AND t.apply_status=#{applyStatus}</if> ",
            "<if test='auditId != null'>AND t.audit_id =#{auditId}</if></script>"})
    Integer countRefund(RefundCountParam param);

    @Select({"<script> SELECT IFNULL(COUNT(1),0)  FROM t_payout WHERE 1=1 ",
            "<if test='belongId != null'> AND  belong_id = #{belongId} </if> ",
            "<if test='applyStatus != null'>  AND  apply_status =  #{applyStatus}</if></script>"})
    Integer countPayout(PayoutCountParam payoutParam);

    @Select({"<script>SELECT IFNULL(count(1),0)  FROM t_site_audit tsa ",
            "WHERE tsa.del_flag = 0  ",
            "<if test='auditStatus != null'>AND tsa.audit_status=#{auditStatus}</if></script>"})
    Integer countSiteAudit(AuditCountParam siteParam);


    // ------------------- 员工数据统计 开始  查询条件公用  --------------------
    //    <sql id="staffTradeWhere">
    //<where>
    //    and ord.staff_id = #{staffId}
    //    and tra.trade_status in (4,2)
    //    and tra.trade_type = 11
    //</where>
    //    </sql>
    /**
     * 获取员工货柜的交易笔数
     */
    String STAFF_TRADE_WHERE_SQL = " where tra.del_flag = 0 and ord.staff_id = #{staffId} and tra.trade_status in (4,2) and tra.trade_type = 11 ";

    @Select({"<script>  select count(*) from t_trade tra left join t_order ord on tra.order_code = ord.order_code ",
            STAFF_TRADE_WHERE_SQL,
            "</script>"})
    int selectTotalCount(StaffTradeAnalysisParam p);

    /**
     * 获取员工货柜的交易总额
     */
    @Select({"<script>select IFNULL(sum(amount),0) from t_trade tra  left join t_order ord on tra.order_code = ord.order_code ",
            STAFF_TRADE_WHERE_SQL,
            "</script>"})
    BigDecimal selectTotalPrice(StaffTradeAnalysisParam p);

    /**
     * 获取本周货柜交易金额
     */
    @Select({"<script>select IFNULL(sum(amount),0) from t_trade tra ",
            "left join t_order ord on tra.order_code = ord.order_code",
            STAFF_TRADE_WHERE_SQL,
            "and YEARWEEK(DATE_FORMAT(tra.pay_time,'%Y-%m-%d'),1) = YEARWEEK(NOW(),1) ",
            " </script>"})
    BigDecimal selectWeekPrice(StaffTradeAnalysisParam p);

    /**
     * 获取上周货柜交易金额
     */
    @Select({"<script>select IFNULL(sum(amount),0) from t_trade tra ",
            "left join t_order ord on tra.order_code = ord.order_code ",
            STAFF_TRADE_WHERE_SQL,
            "and YEARWEEK(DATE_FORMAT(tra.pay_time,'%Y-%m-%d'),1) = YEARWEEK(NOW(),1) -1 ",
            " </script>"})
    BigDecimal selectLastWeekPrice(StaffTradeAnalysisParam p);

    /**
     * 获取本月货柜交易金额
     */
    @Select({"<script>select IFNULL(sum(amount),0) from t_trade tra ",
            "left join t_order ord on tra.order_code = ord.order_code ",
            STAFF_TRADE_WHERE_SQL,
            "and DATE_FORMAT( tra.pay_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) ",
            " </script>"})
    BigDecimal selectMonthPrice(StaffTradeAnalysisParam p);

    /**
     * 获取上月货柜交易金额
     */
    @Select({"<script> select IFNULL(sum(amount),0) from t_trade tra ",
            "left join t_order ord on tra.order_code = ord.order_code ",
            STAFF_TRADE_WHERE_SQL,
            "and DATE_FORMAT(tra.pay_time,'%Y-%m')=DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%Y-%m') ",
            " </script>"})
    BigDecimal selectLastMonthPrice(StaffTradeAnalysisParam p);

    /**
     * 获取一段时间前的货柜交易量
     */
    @Select({"<script>SELECT COUNT(1) from t_trade tra left join t_order ord on tra.order_code = ord.order_code ",
            STAFF_TRADE_WHERE_SQL,
            " and DATE_FORMAT(tra.pay_time,'%Y-%m-%d') >= CURDATE() - INTERVAL #{day} DAY ",
            " </script>"})
    Integer selectCountByDay(StaffTradeAnalysisParam p);

    /**
     * 根据月份获取月度总金额
     */
    @Select({"<script> SELECT IFNULL(SUM(amount),0)  from t_trade tra ",
            "left join t_order ord on tra.order_code = ord.order_code ",
            STAFF_TRADE_WHERE_SQL,
            "AND DATE_FORMAT(tra.pay_time,'%Y-%m') = DATE_FORMAT(#{month},'%Y-%m') ",
            " </script>"})
    BigDecimal selectMonthPriceByMonth(StaffTradeAnalysisParam p);

    /**
     * 根据月份获取月度交易总笔数
     */
    @Select({"<script> select count(*) from t_trade tra left join t_order ord on tra.order_code = ord.order_code ",
            STAFF_TRADE_WHERE_SQL,
            "and DATE_FORMAT(tra.pay_time,'%y-%m') = DATE_FORMAT(#{month},'%y-%m') ",
            " </script>"})
    Integer selectMonthCountPriceByMonth(StaffTradeAnalysisParam p);

    /**
     * 获取月度商品销售排名
     */
    @Select({"<script>select IFNULL(sum(amount),0) as saleAmount , pro.product_name as productName  from t_trade tra ",
            "left join product pro on pro.id = tra.product_id left join t_order ord on tra.order_code = ord.order_code ",
            STAFF_TRADE_WHERE_SQL,
            "and DATE_FORMAT(tra.pay_time,'%y-%m') = DATE_FORMAT(#{month},'%y-%m') ",
            "  group by pro.product_name order by saleAmount desc</script>"})
    List<ProductAmtSumVO> selectMonthProductByMonth(StaffTradeAnalysisParam p);

    /**
     * 获取该月每日的交易额
     */
    @Select({"<script>select DATE_FORMAT( tra.pay_time, '%Y-%m-%d' ) as dayTime, sum( tra.amount ) as amount ",
            "from t_trade tra left join t_order ord on tra.order_code = ord.order_code ",
            STAFF_TRADE_WHERE_SQL,
            "and DATE_FORMAT( tra.pay_time, '%Y-%m' ) = DATE_FORMAT( #{month}, '%Y-%m' ) ",
            "  group by dayTime</script>"})
    List<StaffTradeEveryDayVO> selectDayTradeByMonth(StaffTradeAnalysisParam p);

    /**
     * 获取近几日货柜交易数据
     */
    @Select({"<script>SELECT DATE( date_table.date ) AS dayTime, COUNT( tra.amount ) AS quantity FROM ",
            "(SELECT  @cdate := date_add( @cdate, INTERVAL - 1 DAY ) AS date FROM ",
            "(SELECT @cdate := date_add( date_sub( CURDATE(), INTERVAL 1 DAY ), INTERVAL + 2 DAY ) FROM t_trade ) t0 LIMIT #{day} ) AS date_table ",
            "LEFT JOIN t_trade tra ON DATE( tra.pay_time ) = date_table.date left join t_order ord on tra.order_code = ord.order_code ",
            STAFF_TRADE_WHERE_SQL,
            "group by dayTime</script>"})
    List<StaffTradeEveryDayVO> selectContainerDataDetailByDay(StaffTradeAnalysisParam p);
    // ------------------- 员工数据统计 结束  查询条件公用  --------------------

    // ------------------- 统计扫描 开始  查询条件公用  --------------------
    String SCAN_WHERE_SQL = "<if test='scanUserName != null'> and t.scan_user_name like concat('%',#{scanUserName},'%')</if>" +
            "<if test='containerName != null'> and c.container_name like concat('%',#{containerName},'%')</if>" +
            "<if test='agentName != null'> and au.agent_name like concat('%',#{agentName},'%')</if>" +
            "<if test='siteName != null'> and s.site_name like concat('%',#{siteName},'%')</if>" +
            "<if test='scanTimeS != null'> and t.create_time &gt;= #{scanTimeS}</if>" +
            "<if test='scanTimeE != null'> and t.create_time &lt;= #{scanTimeE}</if>" +
            "<if test='scanPhone != null'> and t.scan_phone like concat('%',#{scanPhone},'%')</if>";

    @Select({"<script>select t.*,c.container_name,au.agent_name,s.site_name from tl_ct_container_scan t,t_container c,s_agent_user au,t_site s ",
            " where t.container_id=c.container_id and c.site_id=s.id and c.agent_id = au.id and c.del_flag=0 ",
            SCAN_WHERE_SQL," ${orderSql}</script>"})
    List<ScanStatisticsVO> scanStatisticsPage(ScanStatisticsParam param);

    @Select({"<script>select DATE_FORMAT(t.create_time, #{sqlDateFormat}) as time,count(t.scan_code) scan_times," ,
            " sum(t.order_times) order_times,sum(t.query_product_times) query_product_times",
            " from tl_ct_container_scan t,t_container c,s_agent_user au,t_site s ",
            " where t.container_id=c.container_id and c.site_id=s.id and c.agent_id = au.id and c.del_flag=0 ",
            SCAN_WHERE_SQL, " group by DATE_FORMAT(t.create_time, #{sqlDateFormat})</script>"})
    List<ScanStatisticsGroupVO> scanStatisticsGroup(ScanStatisticsParam param);

    @Select({"<script> SELECT ifnull( count(*), 0 ) FROM tm_site_location tm  WHERE tm.del_flag = 0 " ,
            " AND tm.belong_id = #{belongId}  AND EXISTS( SELECT con.container_id FROM t_container con LEFT JOIN ",
            " t_container_cell cc ON con.container_id = cc.container_id ",
            " WHERE con.location_id = tm.location_id AND (cc.ceiling_quantity - cc.product_quantity) > 0 AND con.del_flag = 0 )</script>"})
    Integer countCargoLocationByBelongId(Long belongId);

    @Select(" <script>SELECT ifnull(count(1),0) FROM  tl_maintain ma WHERE maintain_status = 1 and staff_id = #{userId} " +
            " <if test ='status == 0'> and DATE(end_time) = DATE(NOW()) </if> </script>")
    Integer countStaffFillCargoNums(@Param("userId") Long userId, @Param("status") Integer status);

    @Select(" <script>SELECT sum(cargo_nums) FROM  tl_maintain ma WHERE maintain_status = 1 and staff_id = #{userId} " +
            " <if test ='status == 0'> and DATE(end_time) = DATE(NOW()) </if> </script>")
    Integer countStaffFillCargoProductNums(@Param("userId") Long userId, @Param("status") Integer status);

    @Select({"<script>SELECT IFNULL( SUM(cargo_nums), 0 ) nums,DATE_FORMAT( start_time, #{sqlDateFormat} ) AS date FROM tl_maintain where del_flag = 0" +
            " <if test='staffId != null'> and staff_id = #{staffId}  </if> " +
            " <if test='startTime != null'> and DATE_FORMAT(start_time,#{sqlDateFormat}) &gt;= DATE_FORMAT(#{startTime}, #{sqlDateFormat}) </if> " +
            " <if test='endTime != null'> and DATE_FORMAT(start_time,#{sqlDateFormat}) &lt;= DATE_FORMAT(#{endTime}, #{sqlDateFormat}) </if> " +
            "GROUP BY date </script>"})
    List<FillCargoTimeDataVO> FillCargoProductNums(FillCargoCountParam param);

    // ------------------- 统计扫描 结束  查询条件公用  --------------------
}
